﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using Npgsql;

namespace Vaffanculo.Presentation.Component.WizardControl.Wizards.WizardsMDX
{
    public sealed class ConectDataBase
    {
        

        public ConectDataBase()
        {
            ConnectionString = String.Format(
                "Server={0};Port={1};Database={2};User={3};Password={4};Encoding={5};Timeout={6};",
                "127.0.0.1", "5432", "Migration", "postgres",
                "datax", "UNICODE","60");
            
            /*NameValueCollection appSettings = ConfigurationManager.AppSettings;
            ConnectionString = String.Format(
                "Server={0};Port={1};Database={2};User={3};Password={4};Encoding={5};Timeout={6};",
                appSettings.Get("IPAddress"), appSettings.Get("Port"),
                appSettings.Get("DataBase"), appSettings.Get("User"),
                appSettings.Get("Password"), appSettings.Get("Encoding"),
                appSettings.Get("Timeout"));*/
        }

        private NpgsqlConnection _npgsqlConnection;

        public string ConnectionString { get; set; }

        public string NameConnection { get; set; }

        private NpgsqlConnection NpgsqlConnection()
        {
            if (_npgsqlConnection != null) 
                return _npgsqlConnection;
            _npgsqlConnection = new NpgsqlConnection(ConnectionString);
            _npgsqlConnection.Open();
            return _npgsqlConnection;
        }

        public bool Transaction(string aQuery)
        {
            NpgsqlTransaction transaction = null;
            try
            {
                _npgsqlConnection.Open();
                transaction = _npgsqlConnection.BeginTransaction();
                var npgsqlCommand = new NpgsqlCommand("", NpgsqlConnection(), transaction);
                npgsqlCommand.ExecuteNonQuery();
                transaction.Commit();
                return true;
            }catch(Exception)
            {
                if(transaction!=null)
                    transaction.Rollback();
                return false;
            }

        }


       
        public DataSet ExecuteQuery(string aQuery)
        {
            lock (this)
            {
                var dataAdapter = new NpgsqlDataAdapter(aQuery, NpgsqlConnection());
                var dataSet = new DataSet();
                try
                {
                    dataAdapter.Fill(dataSet);
                }
                catch (Exception)
                {
                    return null;
                    
                }
                return dataSet;
            }

        }

        public int ExecuteNonQuery(string aQuery)
        {
            lock (this)
            {
                
                var command = new NpgsqlCommand(aQuery, NpgsqlConnection());
                int res = 0;
                try
                {
                    res = command.ExecuteNonQuery();
                }
                catch (Exception)
                {
                    return -3;
                }
                return res;
            }
        }


    }
}
